import threading
from datetime import datetime

import openpyxl

from myapp.elecmain import mysqlconnect

# 全局字典存储定时器
timers = {}

# 获取类型和时间密码
timecode = 0


# xlsx的读取
def load_password_table_from_excel(filename):
    workbook = openpyxl.load_workbook(filename)
    sheet = workbook.active
    password_tables = {}
    for row in sheet.iter_rows(min_row=2, values_only=True):
        row_id = row[0]
        order_id = str(row_id).zfill(3)
        # 提取ABC类密码
        a_passwords = [cell for cell in row[1:26] if isinstance(cell, int)]
        b_passwords = [cell for cell in row[26:51] if isinstance(cell, int)]
        c_passwords = [cell for cell in row[51:76] if isinstance(cell, int)]
        reset_password = row[76] if isinstance(row[76], int) else None

        password_tables[order_id] = {
            'A': a_passwords,
            'B': b_passwords,
            'C': c_passwords,
            'reset': reset_password
        }

    return password_tables


def get_type(times_vlue):
    global timecode
    minute = 60
    ranges = int(times_vlue / minute)
    if 1 <= ranges <= 5:
        class_passwords = "A"
        timecode = ranges
    elif 6 <= ranges <= 10:
        class_passwords = "B"
        timecode = ranges - 5
    elif 11 <= ranges <= 24:
        class_passwords = "C"
        timecode = ranges - 10
        if timecode > 2:
            timecode = 3
    elif 0 < (times_vlue / minute) <= 0.5:
        class_passwords = "C"
        timecode = 4
    else:
        class_passwords = None
        print("没有时间了")
    print("初始类:", class_passwords, "时间密码：", timecode)
    return class_passwords, timecode


def get_device(device_ids):
    connect = mysqlconnect.mysql_connect()
    try:
        with connect.cursor() as cursor:
            sql = "SELECT device_id from password_table where device_id = %s"
            cursor.execute(sql, (device_ids,))
            deid = cursor.fetchone()
            if not deid:
                sql = "INSERT INTO password_table(device_id,a_password,b_password,c_password,reset_password) values (%s, %s, %s, %s, %s)"
                values = (device_ids, 0, 0, 0, 0)
                cursor.execute(sql, values)
                connect.commit()

            sql = "SELECT a_password,b_password,c_password from password_table where device_id = %s"
            cursor.execute(sql, (device_ids,))
            data = cursor.fetchone()
            # 25一次循环
            if int(data[0]) >= 24:
                sql = "UPDATE password_table SET a_password = %s WHERE device_id = %s;"
                cursor.execute(sql, (0, device_ids))
                connect.commit()
            if int(data[1]) >= 24:
                sql = "UPDATE password_table SET b_password = %s WHERE device_id = %s;"
                cursor.execute(sql, (0, device_ids))
                connect.commit()
            if int(data[2]) >= 24:
                sql = "UPDATE password_table SET c_password = %s WHERE device_id = %s;"
                cursor.execute(sql, (0, device_ids))
                connect.commit()

            # 执行查询
            sql = "SELECT * from password_table where device_id = %s"
            cursor.execute(sql, (device_ids,))
            result = cursor.fetchone()
        return result
    except Exception as e:
        print("设备出现错误:", e)


def get_password(deviceid, times_vlue):
    password_table = load_password_table_from_excel(r'C:\django\elecbabydjango\myapp\password\5位数25X3密码表765.xlsx')
    connect = mysqlconnect.mysql_connect()
    datas = get_type(times_vlue)
    class_password = datas[0]
    timecodes = datas[1]
    # 获取当前时间
    current_time = datetime.now()
    print(current_time)
    print("设备号：", deviceid, "初始类：", class_password, "时间密码：", timecodes)
    # 获取ID的后三位
    index = deviceid[-3:]
    print(index)
    if index not in password_table:
        return '序列不匹配'
    # 获取密码信息
    password_set = password_table[index]
    try:
        result = get_device(deviceid)
        # 在设置定时器之前，先取消之前的定时器（如果存在）
        if deviceid in timers:
            timers[deviceid].cancel()

        # 使用 time_value 作为定时器的超时时间
        def on_timer_expire():
            state = 'expire'
            print(f"设备 {deviceid} 的密码已过期。")
            return state

        timer = threading.Timer(int(times_vlue), on_timer_expire)
        timer.start()

        # 将定时器存储到字典中
        timers[deviceid] = timer
        with connect.cursor() as cursor:
            if class_password == 'A':
                a = result[2]
                initial_password = password_set['A'][a]
                class_password_value = a + 1
                sql = "UPDATE password_table SET a_password = %s, use_time = %s  WHERE device_id = %s;"
                cursor.execute(sql, (class_password_value, current_time, deviceid))
                device_password = str(timecodes) + str(initial_password)
                print("设备密码为：", device_password)

                # 设置定时器，假设在60秒后失效
                # timer = threading.Timer(time_vlue, expire_password, args=(device_id,))
                # timer.start()

                connect.commit()
                print("更改成功")
            elif class_password == "B":
                b = result[3]
                sql = "SELECT b_password from password_table where device_id = %s"
                cursor.execute(sql, (deviceid,))
                b_password = cursor.fetchone()
                if int(b_password[0]) >= 24:
                    sql = "UPDATE password_table SET b_password = %s WHERE device_id = %s;"
                    cursor.execute(sql, (0, deviceid))
                    connect.commit()
                initial_password = password_set['B'][b]
                class_password_value = b + 1
                sql = "UPDATE password_table SET b_password = %s WHERE device_id = %s;"
                cursor.execute(sql, (class_password_value, deviceid))
                device_password = str(timecodes) + str(initial_password)
                print("设备密码为：", device_password)
                connect.commit()
                print("更改成功")
            elif class_password == "C":
                c = result[4]
                initial_password = password_set['C'][c]
                class_password_value = c + 1
                sql = "UPDATE password_table SET c_password = %s WHERE device_id = %s;"
                cursor.execute(sql, (class_password_value, deviceid))
                device_password = str(timecodes) + str(initial_password)
                print("设备密码为：", device_password)
                connect.commit()
                print("更改成功")
            else:
                if not class_password:
                    return "没有密码类型"
                else:
                    return "无效的密码类型"

    except Exception as e:
        print("出现错误:", e)
    return device_password

    # print(passwords)
    # sequence_value = device_sequences.get(device_id, 1)  # 默认序列值为1
    #
    # # 获取当前密码
    # password = passwords['A'][sequence_value - 1]  # 从序列中获取密码
    #
    # # 更新序列值
    # sequence_value += 1
    # if sequence_value > 25:
    #     sequence_value = 1
    # device_sequences[device_id] = sequence_value  # 保存新值
    #
    # return password  # 返回密码（可以加上时间码）


# def expire_password(device_id):
#     sql = "UPDATE password_table SET a_password = %s WHERE device_id = %s;"
#     cursor.execute(sql, (0, device_id))
#     connect.commit()
#     print("密码已失效")


# 示例用法
# device_id = "001"
# time_vlue = 180
# password = get_password(device_id, time_vlue)
